package com.hsnn.medstgmini.sys.dao.impl;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.sys.dao.SysPrivilegeDao;
import com.hsnn.medstgmini.sys.model.SysPrivilege;
import com.hsnn.medstgmini.sys.model.SysRole;
import com.hsnn.medstgmini.sys.model.SysUser;

/**
 * @category 权限
 * @author CCL
 */
@Repository
public class SysPrivilegeDaoImpl extends BaseHibernateDAO implements SysPrivilegeDao {

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

	/**
	 * @category 查询权限列表
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysPrivilege> getPrivilegeList(String roleId) {
		Session session = sessionFactory.getCurrentSession();

		StringBuffer sb = new StringBuffer();
		sb.append("select sp.id ");
		sb.append(", sp.name ");
		sb.append(", sp.menu_name ");
		sb.append(", sp.type ");
		sb.append(", sp.classify ");
		sb.append(", sp.icon ");
		sb.append(", sp.sort ");
		sb.append(", sp.url ");
		sb.append(", sp.pid ");
		sb.append(", sp.status ");
		sb.append(", sp.create_user_id ");
		sb.append(", sp.create_datetime ");
		sb.append(", sp.upd_user ");
		sb.append(", sp.upd_datetime ");
		sb.append(", sp.remark ");
		sb.append(", case when srp.role_id IS NULL then 0 else 1 END checked ");
		sb.append("from sys_privilege sp ");
		sb.append("left join sys_role_privilege srp on sp.id = srp.privilege_id and srp.role_id=:roleId ");
		sb.append("where 1 = 1 order by sort asc ");

		SQLQuery query = session.createSQLQuery(sb.toString());
		query.addEntity(SysPrivilege.class);

		roleId = roleId == null ? "" : roleId.trim();
		query.setString("roleId", roleId);

		return query.list();
	}

	/**
	 * @category 保存权限
	 * @param privilege
	 * @return
	 */
	public SysPrivilege savePrivilege(SysPrivilege privilege) {
		Session session = sessionFactory.getCurrentSession();
		if (privilege != null) {
			if (StringUtils.isBlank(privilege.getId())) {
				privilege.setId(UUID.randomUUID().toString());
				privilege.setCreateUserId("admin");
				privilege.setCreateDateTime(new Date());
				session.save(privilege);
			} else {
				SysPrivilege sp = (SysPrivilege) session.get(
						SysPrivilege.class, privilege.getId());
				session.evict(sp);
				privilege.setCreateUserId(sp.getCreateUserId());
				privilege.setCreateDateTime(sp.getCreateDateTime());
				privilege.setUpdUser("admin");
				privilege.setUpdDateTime(new Date());
				session.update(privilege);
			}
			session.flush();
		}
		return privilege;
	}

	/**
	 * @category 删除权限
	 * @param privilege
	 */
	public void deletePrivilege(SysPrivilege privilege) {
		Session session = sessionFactory.getCurrentSession();
		if (privilege != null && StringUtils.isNotBlank(privilege.getId())) {
			deleteChildren(privilege);
			session.delete(privilege);
			session.flush();
		}
	}

	/**
	 * @category 删除子权限
	 * @param privilege
	 */
	@SuppressWarnings("unchecked")
	private void deleteChildren(SysPrivilege privilege) {
		Session session = sessionFactory.getCurrentSession();
		Criteria criteria = session.createCriteria(SysPrivilege.class);
		criteria.add(Restrictions.eq("pid", privilege.getId()));
		List<SysPrivilege> children = criteria.list();
		if (children != null && !children.isEmpty()) {
			for (SysPrivilege sp : children) {
				deleteChildren(sp);
				session.delete(sp);
			}
		}
	}

	/**
	 * @category 查询用户权限列表
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysPrivilege> getPrivilegeListByUser(SysUser user) {
		Session session = sessionFactory.getCurrentSession();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.* FROM sys_privilege a JOIN sys_user_privilege b ON a.id = b.privilege_id ");
		sb.append(" WHERE b.user_id = :userId AND a.status = 1 AND b.type = 0 ");
		sb.append(" order by sort asc ");

		SQLQuery query = session.createSQLQuery(sb.toString());
		query.addEntity(SysPrivilege.class);
		query.setString("userId", user.getId());
		return query.list();
	}

	/**
	 * @category 医疗机构、企业查询子角色权限列表
	 * @date 2015年6月16日
	 * @param role当前登陆人角色
	 * @param roleId当前编辑的子角色的id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysPrivilege> getPrivilegeListByRole(SysRole role, String roleId) {
		Session session = sessionFactory.getCurrentSession();

		StringBuffer sb = new StringBuffer();
		sb.append("select distinct sp.id ");
		sb.append(", sp.name ");
		sb.append(", sp.menu_name ");
		sb.append(", sp.type ");
		sb.append(", sp.classify ");
		sb.append(", sp.icon ");
		sb.append(", sp.sort ");
		sb.append(", sp.url ");
		sb.append(", sp.pid ");
		sb.append(", sp.status ");
		sb.append(", sp.create_user_id ");
		sb.append(", sp.create_datetime ");
		sb.append(", sp.upd_user ");
		sb.append(", sp.upd_datetime ");
		sb.append(", sp.remark ");
		sb.append(", case when srp2.role_id IS NULL then 0 else 1 END checked ");
		sb.append("from sys_privilege sp ");
		sb.append("join sys_role_privilege srp on sp.id = srp.privilege_id and srp.role_id=:roleId ");
		sb.append("left join sys_role_privilege srp2 on sp.id = srp2.privilege_id and srp2.role_id=:roleId2 ");
		sb.append("where 1 = 1 order by sort asc ");

		SQLQuery query = session.createSQLQuery(sb.toString());
		query.addEntity(SysPrivilege.class);

		query.setString("roleId", role.getId());
		query.setString("roleId2", roleId);

		return query.list();
	}

	/**
	 * @category 查询当前登陆用户所属角色的权限（排除新增编辑用户页面所选角色的权限）
	 * @date 2015年6月19日
	 * @param user当前登陆用户
	 * @param roleId新增编辑用户页面所选角色id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SysPrivilege> getExtraPrivilegeList(SysUser user,
			String userId, String roleId) {
		Session session = sessionFactory.getCurrentSession();
		if (user != null && user.getRole() != null && userId != null) {
			StringBuffer sb = new StringBuffer();
			sb.append("select distinct sp.id ");
			sb.append(", sp.name ");
			sb.append(", sp.menu_name ");
			sb.append(", sp.type ");
			sb.append(", sp.classify ");
			sb.append(", sp.icon ");
			sb.append(", sp.sort ");
			sb.append(", sp.url ");
			sb.append(", sp.pid ");
			sb.append(", sp.status ");
			sb.append(", sp.create_user_id ");
			sb.append(", sp.create_datetime ");
			sb.append(", sp.upd_user ");
			sb.append(", sp.upd_datetime ");
			sb.append(", sp.remark ");
			sb.append(", case when sup.user_id IS NULL then 0 else 1 END checked ");
			sb.append("from sys_privilege sp ");
			sb.append("join sys_role_privilege srp on sp.id = srp.privilege_id and srp.role_id=:roleId ");
			sb.append("left join sys_user_privilege sup on sp.id = sup.privilege_id and sup.user_id=:userId ");
			sb.append("where 1 = 1 ");
			if (StringUtils.isNotBlank(roleId)) {
				sb.append("and sp.id not in ");
				sb.append("(select srp2.privilege_id from sys_role_privilege srp2 where srp2.role_id=:roleId2 ) ");
			}
			sb.append("order by sort asc ");

			SQLQuery query = session.createSQLQuery(sb.toString());
			query.addEntity(SysPrivilege.class);

			query.setString("roleId", user.getRole().getId());
			query.setString("userId", userId);
			if (StringUtils.isNotBlank(roleId)) {
				query.setString("roleId2", roleId);
			}

			return query.list();
		}
		return null;
	}

	/**
	 * @category 查询主用户权限集合
	 * @date 2015年10月20日
	 * @param user
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getMainUserPrivilege(String userId,
			String roleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark, ");
		sb.append(" CASE WHEN c.user_id IS NULL THEN 0 ELSE 1 END checked FROM sys_privilege a ");
		sb.append(" JOIN sys_role_privilege b ON a.id = b.privilege_id AND b.role_id = :roleId ");
		sb.append(" LEFT JOIN sys_user_privilege c ON a.id = c.privilege_id AND c.user_id = :userId ");
		sb.append(" WHERE a.status = 1 ORDER BY a.sort ASC ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("roleId", roleId);
		query.setString("userId", userId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 保存用户权限
	 * @date 2015年10月21日
	 * @param userId
	 * @param adds
	 * @param dels
	 */
	public void saveUserPrivilege(String userId, String adds, String dels,Date updTime,String updUser) {
		Session session = sessionFactory.getCurrentSession();
		boolean temp = false; 
		if (StringUtils.isNotBlank(dels)) {
			String sql = "delete from sys_user_privilege where user_id = :userId and privilege_id in (:dels)";
			SQLQuery query = session.createSQLQuery(sql);
			query.setString("userId", userId);
			query.setParameterList("dels", dels.split(","));
			temp=query.executeUpdate()>0?true:false;
		}
		if (StringUtils.isNotBlank(adds)) {
			boolean tag = false; 
			String[] addList = adds.split(",");
			if (addList != null && addList.length > 0) {
				for (String privId : addList) {
					String sql = "insert into sys_user_privilege(user_id, privilege_id, type) values (:userId, :privId, 0)";
					SQLQuery query = session.createSQLQuery(sql);
					query.setString("userId", userId);
					query.setString("privId", privId);
					tag=query.executeUpdate()<0?true:false;
				}
			}
			temp=!tag;
		}
		if(temp){
			writeSysUserPrivilegeLog(userId, updTime, updUser);	
		}
		session.flush();
		
	}
	public void writeSysUserPrivilegeLog(String userId, Date updTime,String updUser) {
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_user_privilege_log  ")
		      .append(" select UUID(),sup.*,:updUser,:updTime from sys_user_privilege sup where sup.user_id= :userId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("userId", userId)
		.setString("updUser", updUser)
		.setParameter("updTime", updTime);
		queryLog.executeUpdate();
		getSession().flush();
	}

	/**
	 * @category 查询主用户权限集合
	 * @date 2015年10月20日
	 * @param user
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getSubUserPrivilege(String userId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark, ");
		sb.append(" CASE WHEN c.user_id IS NULL THEN 0 ELSE 1 END checked FROM sys_privilege a ");
		sb.append(" JOIN sys_role_privilege b ON a.id = b.privilege_id ");
		sb.append(" JOIN sys_user_role d ON b.role_id = d.role_id AND d.user_id = :userId ");
		sb.append(" LEFT JOIN sys_user_privilege c ON a.id = c.privilege_id AND c.user_id = :userId ");
		sb.append(" WHERE a.status = 1 ORDER BY a.sort ASC ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("userId", userId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 查询主角色权限集合
	 * @date 2015年10月20日
	 * @param roleId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getMainRolePrivilege(String roleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark ");
		sb.append(" FROM sys_privilege a JOIN sys_role_privilege b ON a.id = b.privilege_id AND b.role_id = :roleId ");
		sb.append(" WHERE a.status = 1 ORDER BY a.sort ASC ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("roleId", roleId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 查询子角色权限集合
	 * @date 2015年10月20日
	 * @param roleId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getSubRolePrivilege(String roleId,String zhuUserId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark,  ");
		sb.append(" CASE WHEN d.id IS NULL THEN 0 ELSE 1 END checked FROM sys_privilege a  ");
		sb.append(" JOIN sys_user_privilege b  ON a.id = b.privilege_id    ");
		sb.append(" JOIN  sys_user c  ON b.user_id = c.id AND c.id = :userId  ")
		  .append(" JOIN sys_role_privilege D on D.privilege_id=a.id ")
		  .append(" join sys_role srf on srf.id = D.role_id")
		  .append(" join sys_role sr on srf.id = sr.pid ");
		sb.append(" LEFT JOIN (  ");
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark  ");
		sb.append(" FROM sys_privilege a JOIN sys_role_privilege b ON a.id = b.privilege_id AND b.role_id = :roleId WHERE a.status = 1  ");
		sb.append(" ) d ON a.id = d.id WHERE a.status = 1 and sr.id=:roleId ORDER BY a.sort ASC ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("userId", zhuUserId);
		query.setString("roleId", roleId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 保存角色权限
	 * @date 2015年10月22日
	 * @param roleId
	 * @param adds
	 * @param dels
	 */
	public void saveRolePrivilege(String roleId, String adds, String dels,Date updTime,String updUser) {
		Session session = sessionFactory.getCurrentSession();
		if (StringUtils.isNotBlank(dels)) {
			String[] delList = dels.split(",");
			
			// 删除角色的权限
			String sql = "delete from sys_role_privilege where role_id = :roleId and privilege_id in (:dels)";
			SQLQuery query = session.createSQLQuery(sql);
			query.setString("roleId", roleId);
			query.setParameterList("dels", delList);
			query.executeUpdate();

			// 删除角色下用户的权限
			StringBuilder sb = new StringBuilder();
			sb.append(" DELETE FROM sys_user_privilege WHERE privilege_id IN (:dels) AND user_id IN ");
			sb.append(" ( SELECT b.user_id FROM sys_user_role b WHERE b.role_id = :roleId ) ");
			query = session.createSQLQuery(sb.toString());
			query.setString("roleId", roleId);
			query.setParameterList("dels", delList);
			query.executeUpdate();

			// 删除子角色的权限
			sb = new StringBuilder();
			sb.append(" DELETE FROM sys_role_privilege WHERE privilege_id IN (:dels) AND role_id IN ");
			sb.append(" ( SELECT b.id FROM sys_role b WHERE b.pid = :roleId ) ");
			query = session.createSQLQuery(sb.toString());
			query.setString("roleId", roleId);
			query.setParameterList("dels", delList);
			query.executeUpdate();

			// 删除子角色下用户的权限
			sb = new StringBuilder();
			sb.append(" DELETE FROM sys_user_privilege WHERE privilege_id IN (:dels) AND user_id IN ");
			sb.append(" ( SELECT a.user_id FROM sys_user_role a JOIN sys_role b ON a.role_id = b.id AND b.pid = :roleId ) ");
			query = session.createSQLQuery(sb.toString());
			query.setString("roleId", roleId);
			query.setParameterList("dels", delList);
			query.executeUpdate();
		}
		if (StringUtils.isNotBlank(adds)) {
			String[] addList = adds.split(",");
			if (addList != null && addList.length > 0) {
				// 增加角色权限
				StringBuilder sb1 = new StringBuilder();
				sb1.append(" insert into sys_role_privilege(role_id, privilege_id) values ");
				// 增加角色下用户的权限
				StringBuilder sb2 = new StringBuilder();
				sb2.append(" insert into sys_user_privilege(user_id, privilege_id, type) values ");
				// 查询角色下用户的id集合
				String sql = " SELECT a.user_id FROM sys_user_role a JOIN sys_role b ON a.role_id = b.id AND b.id = :roleId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("roleId", roleId);
				List<String> userIds2 = query.list();

				for (int i = 0; i < addList.length; i++) {
					sb1.append(" (:roleId, :privId" + i + ") ");
					if (i < addList.length - 1) {
						sb1.append(", ");
					}

					if (userIds2 != null && !userIds2.isEmpty()) {
						for (int j = 0; j < userIds2.size(); j++) {
							sb2.append(" (:userId" + j + ", :privId" + i + ", 0) ");
							if (j < userIds2.size() - 1) {
								sb2.append(", ");
							}
						}
						if (i < addList.length - 1) {
							sb2.append(", ");
						}
					}
				}

				SQLQuery query1 = session.createSQLQuery(sb1.toString());
				SQLQuery query2 = session.createSQLQuery(sb2.toString());

				for (int i = 0; i < addList.length; i++) {
					String privId = addList[i];
					query1.setString("roleId", roleId);
					query1.setString("privId" + i, privId);
					if (userIds2 != null && userIds2.size() > 0) {
						query2.setString("privId" + i, privId);
					}
				}

				if (userIds2 != null && userIds2.size() > 0) {
					for (int j = 0; j < userIds2.size(); j++) {
						String userId = userIds2.get(j);
						query2.setString("userId" + j, userId);
					}
					query2.executeUpdate();
				}
				    query1.executeUpdate();
			}
		}
		session.flush();
		writeSysRoleRrivilegeLog(roleId,updTime,updUser);	
		
		StringBuffer userSelect = new StringBuffer();
		userSelect.append(" SELECT b.user_id FROM sys_user_role b WHERE b.role_id = :roleId union SELECT a.user_id FROM sys_user_role a JOIN sys_role b ON a.role_id = b.id AND b.pid = :roleId ");
		Query queryUser = getSession().createSQLQuery(userSelect.toString());
		queryUser.setString("roleId", roleId);
		List<String> listUser = queryUser.list();
		for(String userId :listUser){
			writeSysUserPrivilegeLog(userId, updTime, updUser);
		}
		
	}
	
	public void writeSysRoleRrivilegeLog(String roleId, Date updTime,String updUser) {
		StringBuffer hqlLog = new StringBuffer();
		hqlLog.append("insert into sys_role_privilege_log  ")
		      .append(" select UUID(),srp.*,:updUser,:updTime from sys_role_privilege srp where srp.role_id= :roleId ");
		Query queryLog = getSession().createSQLQuery(hqlLog.toString());
		queryLog.setString("roleId", roleId)
		.setString("updUser", updUser)
		.setParameter("updTime", updTime);
		queryLog.executeUpdate();
		getSession().flush();
	}
	/**
	 * @category 查询子角色权限集合（只读）
	 * @date 2015年10月20日
	 * @param roleId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getSubRolePrivilegeAsRead(String roleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark  ");
		sb.append(" FROM sys_privilege a JOIN sys_role_privilege b ON a.id = b.privilege_id AND b.role_id = :roleId ");
		sb.append(" WHERE a.status = 1 ORDER BY a.sort ASC ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("roleId", roleId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

	/**
	 * @category 查询主角色权限集合
	 * @date 2015年10月20日
	 * @param roleId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getMainRolePrivilegeTemp(String roleId) {
		Session session = sessionFactory.getCurrentSession();
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.id, a.name, a.menu_name menuName, a.type, a.classify, a.icon, a.sort, a.url, a.pid, a.status, a.remark, ");
		sb.append(" CASE WHEN b.role_id IS NULL THEN 0 ELSE 1 END checked ");
		sb.append(" FROM sys_privilege a LEFT JOIN sys_role_privilege b ON a.id = b.privilege_id AND b.role_id = :roleId ");
		sb.append(" WHERE a.status = 1 ORDER BY a.sort ASC ");
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setString("roleId", roleId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

}
